/****** Object:  UserDefinedFunction [dbo].[fn_post_count_bycategory]    Script Date: 09/27/2007 23:54:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_post_count_bycategory] (@categoryid uniqueidentifier)

returns int

AS

BEGIN
DECLARE @data int
	SET @data = 0

	SELECT @data=COUNT(p.postid) from forum_post as p
	join forum_topic as t on t.topicid = p.topicid
	join forum_forum as f on f.forumid = t.forumid
	join forum_category as c on c.categoryid = f.categoryid
	WHERE c.categoryid = @categoryid
		
	return @data
END
GO
/****** Object:  UserDefinedFunction [dbo].[fn_post_count_byforum]    Script Date: 09/27/2007 23:54:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_post_count_byforum] (@forumid uniqueidentifier)

returns int

AS

BEGIN
DECLARE @data int
	SET @data = 0

	SELECT @data=COUNT(p.postid) from forum_post as p
	join forum_topic as t on t.topicid = p.topicid
	join forum_forum as f on f.forumid = t.forumid
	WHERE f.forumid = @forumid
		
	return @data
END
GO
/****** Object:  UserDefinedFunction [dbo].[fn_topic_count_bycategory]    Script Date: 09/27/2007 23:54:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_topic_count_bycategory] (@categoryid uniqueidentifier)

returns int

AS

BEGIN
DECLARE @data int
	SET @data = 0

	SELECT @data=COUNT(t.topicid) FROM forum_topic as t
	join forum_forum as f on f.forumid = t.forumid
	join forum_category as c on c.categoryid = f.categoryid
	WHERE c.categoryid = @categoryid
	
	return @data
END
GO
/****** Object:  UserDefinedFunction [dbo].[fn_topic_count_byforum]    Script Date: 09/27/2007 23:54:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_topic_count_byforum] (@forumid uniqueidentifier)

returns int

AS

BEGIN
DECLARE @data int
	SET @data = 0

	SELECT @data=COUNT(t.topicid) from forum_topic as t
	join forum_forum as f on f.forumid = t.forumid
	WHERE f.forumid = @forumid
	
	return @data
END
GO
/****** Object:  UserDefinedFunction [dbo].[fn_post_count_bytopic]    Script Date: 09/27/2007 23:54:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_post_count_bytopic] (@topicid uniqueidentifier)

returns int

AS

BEGIN
DECLARE @data int
	SET @data = 0

	SELECT @data=COUNT(p.postid) from forum_post as p
	join forum_topic as t on t.topicid = p.topicid
	WHERE t.topicid = @topicid
		
	return @data
END
GO
/****** Object:  UserDefinedFunction [dbo].[fn_post_count_byuser]    Script Date: 09/27/2007 23:54:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_post_count_byuser] (@userid uniqueidentifier)

returns int

AS

BEGIN
DECLARE @data int
	SET @data = 0

	SELECT @data=COUNT(postid) from forum_post
	WHERE userid = @userid
		
	return @data
END
GO
/****** Object:  UserDefinedFunction [dbo].[fn_topic_lastpostdate]    Script Date: 09/27/2007 23:54:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_topic_lastpostdate] (@topicid uniqueidentifier)

returns datetime

AS

BEGIN
DECLARE @data datetime
	SET @data = null

	SELECT TOP 1
		@data = p.created
	FROM forum_post as p
	WHERE topicid = @topicid
	ORDER BY p.created DESC
	
	return @data
END
GO
/****** Object:  UserDefinedFunction [dbo].[fn_topic_lastpostid]    Script Date: 09/27/2007 23:54:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_topic_lastpostid] (@topicid uniqueidentifier)

returns uniqueidentifier

AS

BEGIN
DECLARE @data uniqueidentifier
	SET @data = null

	SELECT TOP 1
		@data = p.userid
	FROM forum_post as p
	WHERE topicid = @topicid
	ORDER BY p.created DESC
	
	return @data
END
GO
/****** Object:  UserDefinedFunction [dbo].[fn_combine_posts]    Script Date: 09/27/2007 23:54:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_combine_posts] (@topicid uniqueidentifier)
returns varchar(MAX)
as
begin
declare @data varchar(MAX)
	set @data = ''
	select @data=@data+post+',' from forum_post where topicid = @topicid
	return @data
end
GO
/****** Object:  UserDefinedFunction [dbo].[fn_template_usage_count]    Script Date: 09/27/2007 23:54:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[fn_template_usage_count] (@templateid uniqueidentifier)

returns int

AS

BEGIN
DECLARE @data int
	SET @data = 0

	SELECT @data=COUNT(*) FROM page
	WHERE templateid = @templateid
	
	return @data
END
GO
/****** Object:  UserDefinedFunction [dbo].[fn_newmessage_count]    Script Date: 09/27/2007 23:54:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_newmessage_count] (@userid uniqueidentifier)

returns int

AS

BEGIN
DECLARE @data int
	SET @data = 0

	SELECT @data=COUNT(userprivatemessageid) FROM user_private_message
	WHERE isviewed = 0 and userid = @userid
	
	return @data
END
GO
/****** Object:  UserDefinedFunction [dbo].[fn_post_get_categoryid_byboard]    Script Date: 09/27/2007 23:54:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_post_get_categoryid_byboard] (@boardid uniqueidentifier)

returns uniqueidentifier

AS

BEGIN
DECLARE @data uniqueidentifier
	SET @data = null

	SELECT @data=c.categoryid 
	FROM forum_category as c 
	WHERE c.boardid = @boardid and c.categoryid Is Not Null
		
	return @data
END
GO
/****** Object:  UserDefinedFunction [dbo].[fn_get_username]    Script Date: 09/27/2007 23:54:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_get_username] (@userid uniqueidentifier)

returns nvarchar(max)

AS

BEGIN
DECLARE @data nvarchar(max)
	SET @data = ''

	SELECT @data=username FROM aspnet_users
	WHERE userid = @userid
	
	return @data
END
GO
/****** Object:  UserDefinedFunction [dbo].[fn_blog_count]    Script Date: 09/27/2007 23:54:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_blog_count] (@month int, @year int)

returns int

AS

BEGIN
DECLARE @data int
	SET @data = 0

	SELECT @data=count(blogid)
	FROM blog
	WHERE datepart(m, createdate) = @month
	and datepart(yy, createdate) = @year
	
	return @data
END
GO
/****** Object:  UserDefinedFunction [dbo].[fn_rank_count]    Script Date: 09/27/2007 23:54:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_rank_count] (@itemid uniqueidentifier)

returns int

AS

BEGIN
DECLARE @data int
	SET @data = 0

	SELECT @data=AVG(rank) FROM ranks
	WHERE itemid = @itemid
	
	return @data
END
GO
/****** Object:  UserDefinedFunction [dbo].[fn_search_page]    Script Date: 09/27/2007 23:54:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_search_page] (@pageid uniqueidentifier)
returns varchar(MAX)
as
begin
declare @data varchar(MAX)
	set @data = ''
	select @data=@data+placeholderdata+',' from page_final_data where pageid = @pageid
	return @data
end
GO
/****** Object:  UserDefinedFunction [dbo].[fn_comment_count]    Script Date: 09/27/2007 23:54:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_comment_count] (@itemid uniqueidentifier)

returns int

AS

BEGIN
DECLARE @data int
	SET @data = 0

	SELECT @data=COUNT(id) FROM comments
	WHERE itemid = @itemid
	
	return @data
END
GO
/****** Object:  UserDefinedFunction [dbo].[fn_combine_templatefieldnames]    Script Date: 09/27/2007 23:54:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_combine_templatefieldnames] (@templateid uniqueidentifier)
returns varchar(MAX)
as
begin
declare @data varchar(MAX)
	set @data = ''
	select @data=@data+fieldname+', ' from templates where templateid = @templateid
	order by placeholdernumber
	return @data
end
GO
/****** Object:  UserDefinedFunction [dbo].[fn_view_count]    Script Date: 09/27/2007 23:54:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_view_count] (@itemid uniqueidentifier)

returns bigint

AS

BEGIN
DECLARE @data bigint
	SET @data = 0

	SELECT @data=viewcount FROM views
	WHERE itemid = @itemid
	
	return @data
END 

--grant exec on fn_view_count to poweruser
GO
/****** Object:  UserDefinedFunction [dbo].[fn_tagname_count]    Script Date: 09/27/2007 23:54:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_tagname_count] (@tagname varchar(max))

returns int

AS

BEGIN
DECLARE @data int
	SET @data = 0

	SELECT @data=COUNT(tagname) FROM tags
	WHERE tagname = @tagname
	and archived <> 1
	
	return @data
END
GO
/****** Object:  UserDefinedFunction [dbo].[fn_tagname_count_by_user]    Script Date: 09/27/2007 23:54:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_tagname_count_by_user] (@tagname varchar(max), @userid uniqueidentifier)

returns int

AS

BEGIN
DECLARE @data int
	SET @data = 0

	SELECT @data=COUNT(tagname) FROM tags
	WHERE tagname = @tagname and userid = @userid
	and archived <> 1	
	return @data
END
GO
/****** Object:  UserDefinedFunction [dbo].[fn_tagname_count_by_user_item]    Script Date: 09/27/2007 23:54:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_tagname_count_by_user_item] (@tagname varchar(max), @userid uniqueidentifier, @itemid uniqueidentifier)

returns int

AS

BEGIN
DECLARE @data int
	SET @data = 0

	SELECT @data=COUNT(tagname) FROM tags
	WHERE tagname = @tagname and userid = @userid and itemid = @itemid
	and archived <> 1	
	return @data
END
GO
/****** Object:  UserDefinedFunction [dbo].[fn_tagname_count_by_item]    Script Date: 09/27/2007 23:54:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_tagname_count_by_item] (@tagname varchar(max), @itemid uniqueidentifier)

returns int

AS

BEGIN
DECLARE @data int
	SET @data = 0

	SELECT @data=COUNT(tagname) FROM tags
	WHERE tagname = @tagname and itemid = @itemid
	and archived <> 1	
	return @data
END
GO
/****** Object:  UserDefinedFunction [dbo].[fn_combine_tags]    Script Date: 09/27/2007 23:54:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_combine_tags] (@itemid uniqueidentifier)
returns varchar(MAX)
as
begin
declare @data varchar(MAX)
	set @data = ''
	select @data=@data+tagname+', ' from tags where itemid = @itemid
	return @data
end
GO
